package com.mairuide.transaction;

import com.mairuide.casemanage.service.CaseManageService;
import com.mairuide.entity.PageResult;
import com.mairuide.transactionstreamcount.DealflowVO;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;
import java.util.Map;

@Service
public class BankStatisticsService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private CaseManageService caseManageService;

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public PageResult getBankStatisticsContentList(String bodyCards ,String rivalCard,String casemanageId,String rivalName,String startTime,String endTime,String sortName,String sortOrder,Integer page,Integer size,String keyword){
        String whichTable = caseManageService.getWhichTable(casemanageId);
        StringBuffer sb = new StringBuffer();
        sb.append("select * from "+whichTable+" where casemanage_id = '"+casemanageId+"'");
        if (StringUtils.isNotBlank(bodyCards)){
            sb.append(" AND account_body_card in ("+bodyCards+") ");
        }
        if (StringUtils.isNotBlank(startTime)){
            sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd')>='"+startTime+"'");
        }
        if (StringUtils.isNotBlank(endTime)){
            sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd')<='"+endTime+"'");
        }
        if (StringUtils.isNotBlank(rivalName)){
            sb.append(" AND account_rival like '%"+rivalName+"%'");
        }if (StringUtils.isNotBlank(keyword)){
            sb.append(" AND (account_rival like '%"+keyword+"%' or account_rival_card like '%"+keyword+"%')");
        }
        if ("null".equals(rivalCard)){
            sb.append( " AND account_rival_card is null ");
        }else {
            sb.append( " AND account_rival_card = '"+rivalCard+"'");
        }
        if (StringUtils.isNotBlank(sortName)){
            sb.append(" order by "+sortName+" "+sortOrder);
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());

        Query query = this.entityManager.createNativeQuery(sb.toString());
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        //设置分页
        query.setFirstResult((page-1)*size);
        query.setMaxResults(size);
        //获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list);
        return pageResult;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
	public PageResult getBodyDetail(DealflowVO dealflowVO){
        String whichTable = caseManageService.getWhichTable(dealflowVO.getCasemanageId());
        StringBuffer sb = new StringBuffer();
        String bodyQuery="select * ";
		String countQuery="select count(1) ";
        sb.append(" from "+whichTable+" where is_delete = '0' and casemanage_id = '"+dealflowVO.getCasemanageId()+"'");
        if (StringUtils.isNotBlank(dealflowVO.getBodyNumber())){
            sb.append(" and account_body_number = '"+dealflowVO.getBodyNumber()+"'");
        }
        if (StringUtils.isNotBlank(dealflowVO.getAccountBodyCard())){
            sb.append(" and account_body_card = '"+dealflowVO.getAccountBodyCard()+"'");
        }
        if (dealflowVO.getSign()!=null && dealflowVO.getSign()!=-1){
            sb.append(" and sign = "+dealflowVO.getSign());
        }
        /* 交易开始日期 */
        if(StringUtils.isNotBlank(dealflowVO.getStarTime())) {
            sb.append(" AND TRADE_DATE >= to_date('"+dealflowVO.getStarTime()+"','yyyy-mm-dd') ");
        }

        /* 交易结束日期 */
        if(StringUtils.isNotBlank(dealflowVO.getEndTime())) {
            sb.append(" AND TRADE_DATE <= to_date('"+dealflowVO.getEndTime()+" 23:59:59','yyyy-mm-dd HH24:MI:SS') ");
        }
        if(StringUtils.isNotBlank(dealflowVO.getDetailKeyword())) {
            //对交易类型 摘要 备注 MAC地址 IP地址 查询
            sb.append(" and (content_abstract like '%"+dealflowVO.getDetailKeyword()+"%' or remarks like '%"+dealflowVO.getDetailKeyword()+"%' or mac_address like '%"+dealflowVO.getDetailKeyword()+"%' or ip_address like '%"+dealflowVO.getDetailKeyword()+"%') ");
        }

        if (StringUtils.isNotBlank(dealflowVO.getRivalNumber())){
            if ("null".equals(dealflowVO.getRivalNumber())){
                sb.append(" and  account_rival_card is null ");
            } else {
                sb.append(" and account_rival_card= '"+dealflowVO.getRivalNumber()+"' ");
            }
        }

        if (StringUtils.isNotBlank(dealflowVO.getSort())){
            sb.append(" order by "+dealflowVO.getSort()+" "+dealflowVO.getOrder());
        }

        long count = jdbcTemplate.queryForObject(countQuery+sb.toString(),long.class);
		Query query = this.entityManager.createNativeQuery(bodyQuery+sb.toString());
		//将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		//设置分页
		if(null != dealflowVO.getPage() && null != dealflowVO.getRows()) {
			query.setFirstResult((dealflowVO.getPage() - 1) * dealflowVO.getRows());
			query.setMaxResults(dealflowVO.getRows());
		}
		//获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(count,list);
		return pageResult;
    }

}
